<?php
namespace app\admin\model;

use think\Model;

class Base extends Model{
    protected static $tableAttr = [];
    public function __construct($data = []){
        parent::__construct($data);
        $this->getTableAttr();
    }

    /**
     * @param $start_time   开始时间
     * @param $end_time     结束时间
     * @param array $field  查询参数
     * @param string $fmt   日期
     * @return array|string
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getStatistics($start_time,$end_time,$field=[],$fmt='create_time'){
        if(empty($start_time) || empty($end_time)) {
            return '请确认开始时间start_time或结束时间end_time';
        }
        $field = count($field)>0?$field:'count(`*`)';
        $days = getDaysByRange($start_time,$end_time);
        $list = [];
        for($i = count($days) - 1;$i >= 0;$i--){
            $time = $days[$i];
            $data = $this
                ->field($field)
                ->whereBetweenTime("{$fmt}",$time)
                ->select();
            $data = $data->toArray();
            $list[$time] = $data[0];
        }
        $_data = [];
        foreach ($list as $k => &$v){
            foreach ($v as $k1 => &$v1){
                $v1 = $v1?$v1:0;
                $_data[$k1][$k]=$v1?$v1:0;
            }
        }
        $data['line'] = $_data;
        return ['days'=>$days,'data'=>$list];
    }

    /**
     * @param array $field
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getMouthData($field=[]){
        $days = date('d');
        $days_list = $list = $legend = [];
        $d = date('Y-m-d',time());
        $field = $field?:['COUNT(`id`) AS 发布求助']; //  'COUNT(CASE WHEN `status` = 0 THEN id END) AS 未审核',
        $field = implode(',',$field);
        for($i = $days;$i > 0;$i--){
            $j = $i -1;
            $time = date('Y-m-d',strtotime("{$d} -{$j} day"));
            $count = $this
                ->field($field)
                ->whereBetweenTime('create_time',$time)
                ->select();
            $count = $count->toArray();
            $list[$time] = $count[0];
            array_push($days_list,$time);
        }
        return [
            'days'=>$days_list,
            'data'=>$list,
        ];
    }

    /**
     * @param array $field
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getWeekData($field=[]){
        $days = date('w');
        $days_list = $list = $legend = [];
        $d = date('Y-m-d',time());
        // '1上架 0 下架 -1删除 -2待审核 -3审核失败'
        $field = $field?$field:['COUNT(`id`) AS 总量'];
        $field = implode(',',$field);
        for($i = $days;$i > 0;$i--){
            $j = $i -1;
            $time = date('Y-m-d',strtotime("{$d} -{$j} day"));
            $count = $this
                ->field($field)
                ->whereBetweenTime('create_time',$time)
                ->select();
            $count = $count->toArray();
            $list[$time] = $count[0];
            array_push($days_list,$time);
        }
        return [
            'days'=>$days_list,
            'data'=>$list,
        ];
    }

    /**
     * 分页数据
     * @param array $where
     * @param string $order
     * @param int $limit
     * @param string $field
     * @param bool $out
     * @return \think\Paginator
     * @throws \think\exception\DbException
     */
    public function getList($where=[],$order='',$limit=10,$field='',$out=false){
        $column = $unshow = [];
        if(!$field){
            $field = $this->getTableFields();
        }
        $_fields = $this
            ->table('think_table_fields')
            ->field('name,table_name,table_fields_sort,unshow')
            ->where('name','eq',$this->getTable())
            ->find();
        if($_fields) {
            $_field = \Qiniu\json_decode($_fields['table_fields_sort'], true);
            foreach ($_field as $v) {
                 array_push($column, $v);
            }
            $unshow = $_fields['unshow']?explode(',',$_fields['unshow']):[];
        }else{
            $column = self::$tableAttr['column'];
        }
        $_field = $_field1 = [];
        foreach ($column as $k => $v) {
            if($this->getPk()==$k){
                continue;
            }
            if($out && !in_array($k,$field)){
                if(strpos($k,'_time')!==false){
                    array_push($_field1,$v);
                }else{
                    array_push($_field,$v);
                }
            }elseif(!$out && in_array($k,$field)){
                if(strpos($k,'_time')!==false){
                    array_push($_field1,$v);
                }else{
                    array_push($_field,$v);
                }
            }else{
                continue;
            }
        }
        $list = $this
            ->field($field,$out)
            ->where($where)
            ->order($order)
            ->paginate($limit);
        $page = $list->currentPage();
        $total = $list->total();
        foreach ($list as $k => $v){
            $list[$k]['sort_asc'] = $k + 1 + ($page-1) * $limit;
            $list[$k]['sort_desc'] = $total - ($page-1) * $limit - $k;
        }
        $list = $list->toArray();
        array_unshift($_field,['name'=>$this->getPk(),'title'=>'ID']);
        $_field = array_merge($_field,$_field1);
        $result_field = [];
        foreach ($_field as $k => $v){
            if(in_array($v['name'],$unshow)){
                continue;
            }else{
                if($v['title']){
                    $_tmp = explode(':',$v['title']);
                    if(strpos($_tmp[0],',')!==false){
                        $_tmp1 = explode(',', $_tmp[0]);
                        $tmp =$_tmp1[0];
                    }else{
                        $tmp = $_tmp[0];
                    }
                }else{
                    $tmp = '';
                }
                $v['title'] = $tmp;
                array_push($result_field,$v);
            }
        }
        $list['field_list'] = $result_field;
        $list['field_unshow'] = $unshow;
        return $list;
    }

    /**
     * @return array|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getColumnComment(){
        $list = $this->table('information_schema.COLUMNS')
            ->field('column_name as column_name,column_comment as column_comment,data_type as data_type')
            ->where('TABLE_NAME','eq',$this->getTable())
            ->select();
        self::$tableAttr['column_comment'] = $list;
        return $list;
    }

    /**
     * 下载
     * @param array $where
     * @param string $field
     * @param string $order
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function export($where=[],$field='',$order=''){
        $field = $field ? $field : $this->getTable().'.*';
        $list = $this
            ->field($field)
            ->where($where)
            ->order($order)
            ->select();
        $total = count($list->toArray());
        foreach ($list as $k => $v){
            $list[$k]['sort'] = $total - $k;
        }
        return $list->toArray();
    }

    /**
     * 获取信息
     * @param int $id
     * @param string $field
     * @return array|\PDOStatement|string|Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getOneById($id=0,$field=''){
        $field = $field?$field:$this->getTableFields();
        $info = $this->field($field)->find($id);
        return $info;
    }

    /**
     * 获取表信息
     * @return array
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public function getTableAttr(){
        $sql = "SELECT column_name as column_name,column_comment as column_comment  FROM information_schema.COLUMNS WHERE TABLE_NAME = '{$this->getTable()}' and TABLE_SCHEMA='{$this->getConfig()['database']}'";
        $sql1 = "SELECT table_name as table_name,table_comment as table_comment FROM information_schema.TABLES WHERE TABLE_NAME='{$this->getTable()}'";
        $result = $this->query($sql);
        $result1 = $this->query($sql1);
        $_list = [];
        foreach ($result as $k => $v){
            $tmp['name'] = $v['column_name'];
            if($v['column_comment']){
                $_tmp = explode(':',$v['column_comment']);
                if(strpos($_tmp[0],',')!==false){
                    $_tmp1 = explode(',', $_tmp[0]);
                    $tmp['title'] =$_tmp1[0];
                }else{
                    $tmp['title'] = $_tmp[0];
                }
            }else{
                $tmp['title'] = '';
            }
            $_list[$v['column_name']] = $tmp;
            ksort($_list);
        }
        if($result1){
            $result1 = $result1[0];
        }
        $data = [
            'column'=>$_list,
            'column1'=>$result
        ];
        $data = array_merge($data,$result1);
        return self::$tableAttr = $data;
    }

    /**
     * 表注释
     * @return mixed
     */
    public function getTableComment(){
        return str_replace('表','',self::$tableAttr['table_comment']);
    }

    /**
     * 表名
     * @return mixed
     */
    public function getTableName(){
        return self::$tableAttr['table_name'];
    }

    /**
     * 插入数据
     * @throws \think\db\exception\BindParamException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function getTablesAttr(){
        $_fields = [];
        $fields = $this->field('name,table_name')->select();
        if($fields){
            foreach ($fields as $v){
                array_push($_fields,$v['name']);
            }
        }
        $data = [];
        $db = $this->getConfig()['database'];
        $sql1 = "SELECT table_name,table_comment FROM information_schema.TABLES WHERE TABLE_SCHEMA='{$db}'";
        $result1 = $this->query($sql1);
        foreach ($result1 as $v){
            if(!in_array($v['table_name'],$_fields)){
                $sql = 'SELECT column_name as name,column_comment as title FROM information_schema.COLUMNS '.
                    'WHERE TABLE_NAME = \''.$v['table_name'].'\' and TABLE_SCHEMA = \''.$db.'\'';
                $result = $this->query($sql);
                array_push($data,[
                    'name'=>$v['table_name'],
                    'table_name'=>$v['table_comment'],
                    'table_fields_sort'=> json_encode($result)
                ]);
            }
        }
        if($data){
            $this->saveAll($data);
        }
    }

}